using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
using log4net.Config;
using log4net;

namespace automobili_data
{
    class SQLExecutor
    {
        public static SQLExecutor instance = new SQLExecutor();

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbCmd"></param>
        public delegate void ResultProcessor(IDataReader dr);

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbCmd"></param>
        /// <param name="result"></param>
        public delegate void CommandExecutor(IDbCommand dbCmd, ResultProcessor result);

        protected SQLExecutor()
        {
        }

        protected ILog logger = LogManager.GetLogger(typeof(SQLExecutor));

        /// <summary>
        /// 
        /// </summary>
        /// <param name="cmdExec"></param>
        /// <param name="queryTemplate"></param>
        /// <param name="dbParams"></param>
        protected void execute(CommandExecutor cmdExec, ResultProcessor rProc, String queryTemplate, params QueryParam[] dbParams)
        {
            IDbConnection conn = null;
            IDbCommand cmd = null;
            IDbTransaction trans = null;
            try
            {
                logger.Info("Oppening a connection...");
                conn = AutomobiliDataFactory.instance.createConnection();
                conn.Open();
                trans = conn.BeginTransaction();

                logger.Info("Creating a command...");
                cmd = conn.CreateCommand();
                cmd.CommandText = queryTemplate;

                if (logger.IsDebugEnabled)
                    logger.Debug("Setting parameters...");

                foreach (QueryParam p in dbParams)
                {
                    IDbDataParameter param = cmd.CreateParameter();
                    param.ParameterName = p.ParamName;
                    param.DbType = p.ParamType;
                    param.Value = p.Value;
                    cmd.Parameters.Add(param);
                }

                cmd.Prepare();
                logger.Info("Executing the query...");
                cmdExec(cmd, rProc);

                trans.Commit();
                logger.Info("Done.");

            }
            catch (InvalidOperationException e)
            {
                if (trans != null)
                    trans.Rollback();
                throw e;
            }
            finally
            {
                conn.Close();
            } 
        }

        public void executeSelect(ResultProcessor rProc, String queryTemplate, params QueryParam[] dbParams)
        {
            IDbConnection conn = null;
            IDbCommand cmd = null;
            try
            {
                logger.Info("Oppening a connection...");
                conn = AutomobiliDataFactory.instance.createConnection();
                conn.Open();

                logger.Info("Creating a command...");
                cmd = conn.CreateCommand();
                cmd.CommandText = queryTemplate;

                if (logger.IsDebugEnabled)
                    logger.Debug("Setting parameters...");

                foreach (QueryParam p in dbParams)
                {
                    IDbDataParameter param = cmd.CreateParameter();
                    param.ParameterName = p.ParamName;
                    param.DbType = p.ParamType;
                    param.Value = p.Value;
                    cmd.Parameters.Add(param);
                }

                cmd.Prepare();
                logger.Info("Executing the query...");
                using (IDataReader dr = cmd.ExecuteReader())
                {
                    rProc(dr);
                }
                
                
                logger.Info("Done.");

            }
            finally
            {
                conn.Close();
            }
        }


        public DataTable executeSelectDT(String queryTemplate, params QueryParam[] dbParams)
        {
            IDbConnection conn = null;
            IDbCommand cmd = null;
            DataTable result = null;
            try
            {
                logger.Info("Oppening a connection...");
                conn = AutomobiliDataFactory.instance.createConnection();
                conn.Open();

                logger.Info("Creating a command...");
                cmd = conn.CreateCommand();
                cmd.CommandText = queryTemplate;

                if (logger.IsDebugEnabled)
                    logger.Debug("Setting parameters...");

                foreach (QueryParam p in dbParams)
                {
                    IDbDataParameter param = cmd.CreateParameter();
                    param.ParameterName = p.ParamName;
                    param.DbType = p.ParamType;
                    param.Value = p.Value;
                    cmd.Parameters.Add(param);
                }

                cmd.Prepare();
                logger.Info("Executing the query...");
                using (IDataReader dr = cmd.ExecuteReader())
                {
                    result = new DataTable(dr.GetSchemaTable().TableName);
                    result.Load(dr);
                }

                logger.Info("Done.");
                return result;
            }
            finally
            {
                conn.Close();
            }
        }


        public void update(String queryTemplate, params QueryParam[] dbParams)
        {
            execute(new CommandExecutor(executeUpdate), null, queryTemplate, dbParams);
        }

        protected void executeUpdate(IDbCommand cmd, ResultProcessor result)
        {
            cmd.ExecuteNonQuery();
        }

        protected void executeSelect(IDbCommand cmd, ResultProcessor readResult)
        {
            IDataReader dbr = cmd.ExecuteReader();
            if (readResult != null)
                readResult(dbr);
        }

        public void select(String queryTemplate, ResultProcessor readResult, params QueryParam[] dbParams)
        {
            execute(new CommandExecutor(executeSelect), readResult, queryTemplate, dbParams);
        }

        public void execute()
        {
            update("INSERT INTO student(ime, prezime) Values(@p1,@p2)",
                new QueryParam("@p1", DbType.String, "Nemanja"),
                new QueryParam("@p2", DbType.String, "Kojic"));
            select("SELECT * FROM student", null);
        }
    }

    
}
